IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaveAdvanceFundRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SaveAdvanceFundRequest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--SaveAdvanceFundRequest 41359, '2009A056877', 'Testing again and again...', 2

CREATE PROCEDURE [dbo].[SaveAdvanceFundRequest] 
(  
	@fwkDomainUserId	INT,
	@PropNumber	VARCHAR(50),
	@Comment	VARCHAR(2000),
	@StatusType	INT
)  
AS 

SET NOCOUNT ON

		DECLARE @AFRId INT
		DECLARE @AFREventId INT

		IF @StatusType = 1
		BEGIN
			SET @AFRId = (SELECT Id FROM dbo.AdvanceFundRequest WHERE PropNumber = @PropNumber)
			IF (ISNULL(@AFRId, 0) = 0)
			BEGIN
				BEGIN TRANSACTION

					INSERT INTO dbo.AdvanceFundRequest (PropNumber, DateCreated, CreatedBy)
					VALUES (@PropNumber, GETDATE(), @fwkDomainUserId)
			
					IF @@ERROR <> 0 
					BEGIN
						-- Rollback the transaction
						ROLLBACK
						RAISERROR ('Error in saving the Fund Request.', 16, 1)
						RETURN
					END

					SET @AFRId = (SELECT Id FROM dbo.AdvanceFundRequest WHERE PropNumber = @PropNumber)

					INSERT INTO dbo.AdvanceFundRequestComments (AdvanceFundRequestId, AdvanceFundRequestStatusTypeId, Comment, CommentedBy, DateCommented)
					VALUES (@AFRId, @StatusType, @Comment, @fwkDomainUserId, GETDATE())

					IF @@ERROR <> 0 
					BEGIN
						-- Rollback the transaction
						ROLLBACK
						RAISERROR ('Error in saving the Fund Request.', 16, 1)
						RETURN
					END

					SET @AFREventId = (SELECT Id FROM dbo.AdvanceFundRequestEvent WHERE [Name] = 'Draft')
					INSERT INTO dbo.AdvanceFundRequestEventHistory (AdvanceFundRequestId, AdvanceFundRequestEventId, CreatedBy, DateCreated)
					VALUES (@AFRId, @AFREventId, @fwkDomainUserId, GETDATE())

					IF @@ERROR <> 0 
					BEGIN
						-- Rollback the transaction
						ROLLBACK
						RAISERROR ('Error in saving the Fund Request.', 16, 1)
						RETURN
					END

					INSERT INTO dbo.AdvanceFundRequestWorkFlowStatus (AdvanceFundRequestId, AdvanceFundRequestStatusTypeId, DateStart, CreatedBy)
					VALUES (@AFRId, @StatusType, GETDATE(), @fwkDomainUserId)

					IF @@ERROR <> 0 
					BEGIN
						-- Rollback the transaction
						ROLLBACK
						RAISERROR ('Error in saving the Fund Request.', 16, 1)
						RETURN
					END

					COMMIT TRANSACTION
			END
			ELSE
			BEGIN
				BEGIN TRANSACTION

				IF @Comment <> ''
				BEGIN
					--INSERT INTO dbo.AdvanceFundRequestComments (AdvanceFundRequestId, AdvanceFundRequestStatusTypeId, Comment, CommentedBy, DateCommented)
					--VALUES (@AFRId, @StatusType, @Comment, @fwkDomainUserId, GETDATE())
					UPDATE dbo.AdvanceFundRequestComments SET Comment = @Comment
						WHERE AdvanceFundRequestId = @AFRId
						
					IF @@ERROR <> 0 
					BEGIN
						-- Rollback the transaction
						ROLLBACK
						RAISERROR ('Error in saving the Fund Request.', 16, 1)
						RETURN
					END
				END

				SET @AFREventId = (SELECT Id FROM dbo.AdvanceFundRequestEvent WHERE [Name] = 'Draft')
				INSERT INTO dbo.AdvanceFundRequestEventHistory (AdvanceFundRequestId, AdvanceFundRequestEventId, CreatedBy, DateCreated)
				VALUES (@AFRId, @AFREventId, @fwkDomainUserId, GETDATE())

				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				COMMIT TRANSACTION	
			END	
		END				
		ELSE 
		BEGIN			
			SET @AFRId = (SELECT Id FROM dbo.AdvanceFundRequest WHERE PropNumber = @PropNumber)
			IF (ISNULL(@AFRId, 0) = 0)
			BEGIN
				BEGIN TRANSACTION
				INSERT INTO dbo.AdvanceFundRequest (PropNumber, DateSubmitted, SubmittedBy, DateCreated, CreatedBy)
					VALUES (@PropNumber, GETDATE(), @fwkDomainUserId, GETDATE(), @fwkDomainUserId)
	 
				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				SET @AFRId = (SELECT Id FROM dbo.AdvanceFundRequest WHERE PropNumber = @PropNumber)
				
				INSERT INTO dbo.AdvanceFundRequestComments (AdvanceFundRequestId, AdvanceFundRequestStatusTypeId, Comment, CommentedBy, DateCommented)
					VALUES (@AFRId, @StatusType, @Comment, @fwkDomainUserId, GETDATE())

				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				--SET @AFREventId = (SELECT Id FROM dbo.AdvanceFundRequestEvent WHERE [Name] = 'Submission')
				INSERT INTO dbo.AdvanceFundRequestEventHistory (AdvanceFundRequestId, AdvanceFundRequestEventId, CreatedBy, DateCreated)
				VALUES (@AFRId, @StatusType, @fwkDomainUserId, GETDATE())

				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				INSERT INTO dbo.AdvanceFundRequestWorkFlowStatus (AdvanceFundRequestId, AdvanceFundRequestStatusTypeId, DateStart, CreatedBy)
				VALUES (@AFRId, @StatusType, GETDATE(), @fwkDomainUserId)

				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				COMMIT TRANSACTION
			END
			ELSE
			BEGIN
				BEGIN TRANSACTION
				UPDATE dbo.AdvanceFundRequest SET DateSubmitted = GETDATE(), SubmittedBy = @fwkDomainUserId
				WHERE PropNumber = @PropNumber
	 
				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				IF @Comment <> ''
				BEGIN
					--INSERT INTO dbo.AdvanceFundRequestComments (AdvanceFundRequestId, AdvanceFundRequestStatusTypeId, Comment, CommentedBy, DateCommented)
					--VALUES (@AFRId, @StatusType, @Comment, @fwkDomainUserId, GETDATE())
					UPDATE dbo.AdvanceFundRequestComments SET Comment = @Comment
						WHERE AdvanceFundRequestId = @AFRId
						
					IF @@ERROR <> 0 
					BEGIN
						-- Rollback the transaction
						ROLLBACK
						RAISERROR ('Error in saving the Fund Request.', 16, 1)
						RETURN
					END
				END

				--SET @AFREventId = (SELECT Id FROM dbo.AdvanceFundRequestEvent WHERE [Name] = 'Submission')
				INSERT INTO dbo.AdvanceFundRequestEventHistory (AdvanceFundRequestId, AdvanceFundRequestEventId, CreatedBy, DateCreated)
				VALUES (@AFRId, @StatusType, @fwkDomainUserId, GETDATE())

				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				INSERT INTO dbo.AdvanceFundRequestWorkFlowStatus (AdvanceFundRequestId, AdvanceFundRequestStatusTypeId, DateStart, CreatedBy)
				VALUES (@AFRId, @StatusType, GETDATE(), @fwkDomainUserId)

				IF @@ERROR <> 0 
				BEGIN
					-- Rollback the transaction
					ROLLBACK
					RAISERROR ('Error in saving the Fund Request.', 16, 1)
					RETURN
				END

				COMMIT TRANSACTION
			END
		END


GO